package com.service.database.handler;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.service.database.config.DataBase;
import com.service.database.constants.JdbcConstants;
import com.service.database.constants.SqlConstants;
import com.service.database.model.DataColumn;
import com.service.database.utils.CommonUtils;
import com.service.database.constants.DbConstants;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * PG数据库处理
 * @author xianggj
 * @Date 2021/6/4 22:16
 **/
public class PostgresHandler extends AbstractDatabaseHandler {

    public PostgresHandler(DataBase type) {
        super(type);
    }

    @Override
    public String urlSpecialDeal(StringBuffer currentUrl) {
        if (StrUtil.isNotBlank(dbName)){
            currentUrl.append(dbName);
        }
        if (CommonUtils.isNotBlank(schema)){
            currentUrl.append(linkStr);
            currentUrl.append(JdbcConstants.CURRENT_SCHEMA);
            currentUrl.append(schema);
            linkStr = JdbcConstants.AND_MARK;
        }
        //用户名和密码连续
        if (StrUtil.isNotBlank(userName) && StrUtil.isNotBlank(pwd)){
            currentUrl.append(linkStr);
            currentUrl.append(JdbcConstants.USER);
            currentUrl.append(userName);
            currentUrl.append(JdbcConstants.PWD);
            currentUrl.append(pwd);
            linkStr = JdbcConstants.AND_MARK;
        }
        return currentUrl.toString();
    }

    @Override
    public String getShowDataBaseSql() {
        return String.format("SELECT datname as %s FROM pg_database " +
                "WHERE datistemplate = false", returnKeys);
    }

    @Override
    public String getShowTablesSql() {
        StringBuffer sql = new StringBuffer();
        sql.append(String.format("SELECT t.tablename AS  %s,b.description AS %s ",returnKeys));
        sql.append("FROM pg_tables t " +
                "LEFT JOIN pg_class a ON a.relname = t.tablename " +
                "LEFT JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid " +
                "WHERE a.relkind='r' ");
        if (!CommonUtils.isNotBlank(schema)) {
            schema = DbConstants.PG_DEFAULT_SCHEMANAME;
        }
        sql.append(String.format("and  t.schemaname = '%s' ", schema));
        //强行加参数
        sql.append("AND '1' != ?  ");
        sql.append("ORDER BY a.relname  ");
        return sql.toString();
    }

    @Override
    public String getShowTableDescSql() {
        StringBuffer sql = new StringBuffer();
        sql.append(String.format("SELECT col.column_name as %s, " +
                "col.udt_name as %s, " +
                "CASE WHEN col.is_nullable = 'YES' THEN '是' ELSE '否' END as %s, " +
                "CASE WHEN k.column_name is not null THEN '是' ELSE '否' END as %s, " +
                "CASE WHEN col.udt_name = 'timestamp' THEN '6' WHEN col.character_maximum_length is not null THEN col.character_maximum_length ELSE col.numeric_precision  END as %s, " +
                "col.numeric_scale as %s, " +
                "com.comments as %s ", returnKeys));
        sql.append("FROM information_schema.columns  col " );
        //求注释
        sql.append("left join ( " +
                "SELECT  a.attname as column_name,  col_description(a.attrelid,a.attnum) as comments,  c.relname as relname  " +
                "from pg_class as c  " +
                "left join pg_attribute as a on a.attrelid = c.oid  " +
                "left join pg_type as t on a.atttypid = t.oid  " +
                "WHERE a.attnum>0) com on com.column_name =col.column_name and com.relname = col.table_name ");
        //求主键
        sql.append("left join ( " +
                "SELECT a.attname AS column_name,c.relname  as relname " +
                "FROM pg_attribute a " +
                "INNER JOIN pg_class c ON a.attrelid = c.oid " +
                "INNER JOIN pg_constraint p ON  p.conrelid = c.oid  " +
                "WHERE p.contype = 'p' and a.attnum = p.conkey[1] " +
                ") k on  k.column_name =col.column_name and k.relname = col.table_name ");
        sql.append("WHERE  col.table_name= ? ");
        if (!CommonUtils.isNotBlank(schema)) {
            schema = DbConstants.PG_DEFAULT_SCHEMANAME;
        }
        sql.append(String.format(" and col.table_schema='%s'", schema));
        return sql.toString();
    }

    @Override
    public String getShowTableDataSql() {
        if (CommonUtils.isNotBlank(schema)){
            return String.format("select * from %s.%s ",  addChar(schema), addChar(tableName));
        }
        return String.format("select * from %s ",  addChar(tableName));
    }

    @Override
    public String getShowTableDataNumSql() {
        if (CommonUtils.isNotBlank(schema)){
            return String.format("select count(0) as %s from %s.%s", returnKeys[0], addChar(schema), addChar(tableName));
        }
        return String.format("select count(0) as %s from %s", returnKeys[0], addChar(tableName));
    }

    @Override
    public String distinctColSql() {
        StringBuffer sql = new StringBuffer("select distinct ");
        List<String> sqls = new ArrayList<>();
        for (int i = 0; i < cols.length; i++) {
            if (returnKeys.length > i){
                sqls.add(String.format(" %s as %s", cols[i], returnKeys[i]));
            } else {
                sqls.add(String.format(" %s", cols[i]));
            }
        }
        sql.append(CollUtil.join(sqls, SqlConstants.COMMA));
        sql.append(String.format(" from %s ",  addChar(tableName)));
        return sql.toString();
    }

    @Override
    public String addLimitBySql(String sql, Integer num, Integer offset) {
        String outSql = CommonUtils.substringNotBracketsString(sql);
        //如果包含limit
        if (StrUtil.containsIgnoreCase(outSql, SqlConstants.LIMIT)){
            sql = String.format(SqlConstants.SELECT_CONTAIN, sql);
        }
        if (num == null){
            return sql;
        }
        sql += SqlConstants.LIMIT + num;
        if (offset != null){
            sql += SqlConstants.OFFSET + offset;
        }
        return  sql;
    }

    @Override
    public String dropTableSql() {
        if (!CommonUtils.isNotBlank(schema)) {
            schema = DbConstants.PG_DEFAULT_SCHEMANAME;
        }
        return String.format("DROP TABLE IF EXISTS %s.%s.%s ",
                addChar(dbName),  addChar(schema), addChar(tableName));
    }

    /**
     *
     * -- ----------------------------
     * -- Primary Key structure for table tb_student
     * -- ----------------------------
     * ALTER TABLE "public"."tb_student" ADD CONSTRAINT "tb_student_pkey" PRIMARY KEY ("id");
     * COMMENT ON COLUMN "public"."tb_student"."id" IS '序号';
     * @param columns
     * @return
     */
    @Override
    public List<String> createTableSql(List<DataColumn> columns) {
        if (!CommonUtils.isNotBlank(schema)) {
            schema = DbConstants.PG_DEFAULT_SCHEMANAME;
        }
        StringBuffer sql = new StringBuffer();
        sql.append("CREATE TABLE  ");
        sql.append(" IF NOT EXISTS ");
        String tableName = String.format(" %s.%s.%s ",
                addChar(dbName), addChar(schema), addChar(this.tableName));
        sql.append(tableName);
        sql.append("(");
        List<String> sqls = new ArrayList<>();
        for (int i = 0; i < columns.size() ; i++) {
            DataColumn dataCol = columns.get(i);
            String columnName = dataCol.getColumnName();
            int dataType = dataCol.getDataTypeInt();
            if (StrUtil.isBlank(columnName) || dataType == 0){
                continue;
            }
            //主键不添加
//            if (dataCol.getColumnKey()){
//                //     * ALTER TABLE "public"."tb_student" ADD CONSTRAINT "tb_student_pkey" PRIMARY KEY ("id");
//                sqls.add(" ALTER TABLE  " +tableName + " ADD CONSTRAINT \""+ this.tableName +"_pkey\" PRIMARY KEY (" + addChar(columnName)+")");
//            }

            sql.append(addChar(columnName));
            sql.append(" ");
            addTypeStr(sql, dataCol);
            if (dataCol.getIsNull()){
                sql.append(" NULL ");
            } else {
                sql.append(" NOT NULL ");
            }
            if (i != columns.size() -1){
                sql.append(",");
            }
            if (StrUtil.isNotBlank(dataCol.getColumnComment())){
                //     * COMMENT ON COLUMN "public"."tb_student"."id" IS '序号';
                sqls.add("COMMENT ON COLUMN "+ tableName + "." + addChar(columnName) + " IS '"+StrUtil.removeAll(dataCol.getColumnComment(), '\'')+"'");
            }
        }
        sql.append(")");
        List<String> list = new ArrayList<>();
        list.add(sql.toString());
        list.addAll(sqls);
        return list;
    }

    private void addTypeStr(StringBuffer sql, DataColumn dataCol) {
        String typeStr = intToType(dataCol.getDataTypeInt());
        Integer length = dataCol.getLength();
        Integer floatLength = dataCol.getFloatLength();
        switch (typeStr){
            case "varchar":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = SqlConstants.DEFAULT_VARCHAR_LENGTH;
                }
                sql.append(length);
                sql.append(")");
                break;
            case "char":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = 5;
                }
                sql.append(length);
                sql.append(")");
                break;
            case "timestamp":
                sql.append(typeStr);
                sql.append("(6)");
                break;
            default:
                sql.append(typeStr);
        }
    }


    @Override
    protected String intToType(int i) {
        switch (i){
            case Types.INTEGER:
                return "int4";
            case Types.BOOLEAN:
                return  "bool";
            case Types.CHAR:
                return  "char";
            case Types.TIME:
                return "time";
            case Types.DATE:
                return "date";
            case Types.TIMESTAMP:
            case Types.TIMESTAMP_WITH_TIMEZONE:
                return "timestamp";
            case Types.TIME_WITH_TIMEZONE:
                return "timetz";
            case Types.BIGINT:
                return "int8";
            case Types.DECIMAL:
                return "decimal";
            case Types.DOUBLE:
                return "float8";
            case Types.FLOAT:
            case Types.REAL:
                return "float4";
            case Types.BINARY:
                return "bit";
            default:
                return "varchar";
        }
    }
}
